﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DISDisplay
{
    class DataBase
    {
        private string server = null;
        private string user = null;
        private string pass = null;
        private string database = null;
        private string dbcon = null;
        private static SqlConnection m_cnADONetConnection;
        private static SqlDataAdapter Ad_ms_Table;
        private SqlCommandBuilder CmBl_ms_Table;

        public DataBase(string server, string user, string pass, string database)
        {
            this.server = server;
            this.user = user;
            this.pass = pass;
            this.database = database;
            dbcon = "server=" + this.server + ";uid=" + this.user + ";pwd=" + this.pass + ";database=" + this.database;
        }

        public DataBase(string server, string database)
        {
            this.server = server;
            this.database = database;
            dbcon = "server=" + this.server + ";database=" + this.database + ";Trusted_Connection=yes";
        }

        public bool checkConnection()
        {
            bool chk = true;
            try
            {
                m_cnADONetConnection = new SqlConnection(dbcon);
                m_cnADONetConnection.Open();
                m_cnADONetConnection.Close();
            }
            catch (SqlException ex) { chk = false; }
            return chk;
        }

        public DataTable getTable(string sql)
        {
            DataTable Tb_Table = new DataTable();
            try
            {
                m_cnADONetConnection = new SqlConnection(dbcon);
                Ad_ms_Table = new SqlDataAdapter();
                CmBl_ms_Table = new SqlCommandBuilder(Ad_ms_Table);
                Tb_Table = new DataTable();
                Ad_ms_Table.SelectCommand = new SqlCommand(sql, m_cnADONetConnection);
                Tb_Table.Clear();
                Ad_ms_Table.Fill(Tb_Table);
            }
            catch (Exception ex) { }
            return Tb_Table;
        }

        public void sqlExecute(string sql)
        {
            m_cnADONetConnection = new SqlConnection(dbcon);
            SqlCommand cmd = new SqlCommand(sql, m_cnADONetConnection);
            try
            {
                m_cnADONetConnection.Open();
                cmd.ExecuteNonQuery();
                m_cnADONetConnection.Close();
            }
            catch (SqlException e)
            {
                Console.WriteLine("Error: {0}", e.Errors[0].Message);
            }

        }
    }
}
